# coding=utf-8

from data import *

if __name__ == "__main__":

    wb = load_workbook(filename=u'data0604/财政部代发1.xlsx')
    ws = wb[u"流标以前"]

    columns_number = dict()
    i = 1
    while ws.cell(row=1, column=i).value is not None:
        columns_number[ws.cell(row=1, column=i).value] = i
        i = i + 1

    i = 1
    while ws.cell(row=i, column=1).value is not None:
        i = i + 1
    total_line_number = i - 1

    fill_years(ws, tuple(ws.columns), columns_number, total_line_number)

    '''
    fill_column(ws, tuple(ws.columns), columns_number, total_line_number, u"财政收入增长率", make_dict(u"财政收入增长率.xlsx", 'Sheet2'))
    fill_column(ws, tuple(ws.columns), columns_number, total_line_number, u"GDP", make_dict(u"GDP，增速，人均GDP.xlsx", 'GDP'))
    fill_column(ws, tuple(ws.columns), columns_number, total_line_number, u"GDP增速", make_dict(u"GDP，增速，人均GDP.xlsx", u'GDP增速'))
    fill_column(ws, tuple(ws.columns), columns_number, total_line_number, u"人均GDP", make_dict(u"GDP，增速，人均GDP.xlsx", u'人均GDP'))
    fill_column(ws, tuple(ws.columns), columns_number, total_line_number, u"债务负担率", make_dict(u"债务负担率.xlsx", 'Sheet3'))
    fill_column(ws, tuple(ws.columns), columns_number, total_line_number, u"地方公共财政收入", make_dict(u"地方公共财政收入.xlsx", 'Sheet2'))
    fill_column(ws, tuple(ws.columns), columns_number, total_line_number, u"财政收入增长率", make_dict(u"财政收入增长率.xlsx", 'Sheet2'))
    '''

    fill_column(ws,tuple(ws.columns), columns_number, total_line_number, u"政府性基金收入",
                make_dict(u"data0604/政府性基金收入.xlsx", u'本级3'))
    fill_column(ws, tuple(ws.columns), columns_number, total_line_number, u"GDP",
                make_dict(u"data0604/GDP，增速，人均GDP.xlsx", 'GDP'))
    fill_column(ws, tuple(ws.columns), columns_number, total_line_number, u"GDP增速",
                make_dict(u"data0604/GDP，增速，人均GDP.xlsx", u'GDP增速'))
    fill_column(ws, tuple(ws.columns), columns_number, total_line_number, u"人均GDP",
                make_dict(u"data0604/GDP，增速，人均GDP.xlsx", u'人均GDP'))
    fill_column(ws, tuple(ws.columns), columns_number, total_line_number, "CPI",
                make_dict("data0604/CPI.xlsx", 'Sheet2'))
    fill_column(ws, tuple(ws.columns), columns_number, total_line_number, u"债务负担率",
                make_dict(u"data0604/债务负担率.xlsx", u'用新世纪'))
    fill_column(ws, tuple(ws.columns), columns_number, total_line_number, u"财政收入增长率",
                make_dict(u"data0604/财政收入增长率.xlsx", 'Sheet2'))
    fill_column(ws, tuple(ws.columns), columns_number, total_line_number, u"财政自给率",
                make_dict(u"data0604/财政自给率.xlsx", 'Sheet3'))
    fill_column(ws, tuple(ws.columns), columns_number, total_line_number, u"地方公共财政收入",
                make_dict(u"data0604/地方公共财政收入.xlsx", 'Sheet3'))

    # 下面对到期收益率做处理
    wb2 = load_workbook(u"data0604/中债国债到期收益率.xlsx")
    ws2 = wb2['Sheet1']

    cols = tuple(ws2.columns)
    YTM = {}

    for index, date in enumerate(cols[0][1:]):
        YTM[date.value] = {}
        YTM[date.value][1] = cols[1][index+1].value
        YTM[date.value][2] = cols[2][index+1].value
        YTM[date.value][3] = cols[3][index+1].value
        YTM[date.value][5] = cols[4][index+1].value
        YTM[date.value][7] = cols[5][index+1].value
        YTM[date.value][10] = cols[6][index+1].value

    cols = tuple(ws.columns)
    for index, (date, year) in enumerate(zip(cols[4][1:], cols[7][1:])):
        loop = True
        try_date = date.value
        try_value = None
        while loop:
            try:
                try_value = YTM[try_date][year.value]
            except KeyError:
                try:
                    try_date = datetime.datetime(try_date.year, try_date.month, try_date.day - 1)
                except AttributeError:
                    break
            else:
                loop = False
        ws.cell(column=30, row=index + 2, value=try_value)
    wb.save(u'0604结果4.xlsx')